﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using MySql.Data.MySqlClient;

namespace Plazos
{
    class SemestreBD : DBConnect
    {
        protected int idSem;
        protected string nombreSem;
        protected string fechaConvenio;
        private string query;
        private MySqlCommand cmd;
        protected String error = "0";

        public bool guardar()
        {
            //revisar que no existe..
            query = "SELECT COUNT(*) FROM semestre WHERE nombreSem = '" + nombreSem + "';";
            if (this.OpenConnection() == true)
            {
                cmd = new MySqlCommand(query, connection);
                try
                {
                    int cant = Convert.ToInt32(cmd.ExecuteScalar());
                    if (cant > 0)
                    {
                        error = "El semestre que intenta ingresar ya existe en el sistema";
                        CloseConnection();
                        return false;
                    }
                    else
                    {
                        //insertar
                        query = "INSERT INTO semestre (nombreSem, fechaConvenio) VALUES('" + nombreSem + "','" + fechaConvenio + "')";
                        Console.WriteLine(query);
                        //create command and assign the query and connection from the constructor
                        cmd = new MySqlCommand(query, connection);

                        //Execute command
                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine(e.Message);
                            error = e.Message;
                            this.CloseConnection();
                            return false;
                        }

                        //close connection
                        this.CloseConnection();
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                    error = e.Message;
                    this.CloseConnection();
                    return false;
                }
                return true;
            }
            else
            {
                return false;
            }
        }

       


        public List<Semestre> getSemestres(){
            List<Semestre> semestres = new List<Semestre>();
            query = "SELECT * FROM semestre";
            if (OpenConnection())
            {
                cmd = new MySqlCommand(query,connection);
                try
                {
                    MySqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        semestres.Add(new Semestre(dr.GetInt32(0), dr.GetString(1), dr.GetString(2)));
                    }
                    dr.Close();
                }
                catch (Exception e)
                {
                    Console.WriteLine("["+System.Reflection.MethodBase.GetCurrentMethod().Name+"] " + e.Message);
                }
                finally
                {
                    CloseConnection();
                }
            }
            return semestres;

        }

        public Semestre getSemestre(String ano, String periodo)
        {
            String n = "";
            n += (periodo.Equals("1")) ? "Primero" : "Segundo";
            n += " " + ano;
            Semestre s = null ;
            query = "SELECT * FROM semestre WHERE nombreSem = n";
            if (OpenConnection())
            {
                cmd = new MySqlCommand(query, connection);
                try
                {
                    MySqlDataReader dr = cmd.ExecuteReader();
                    dr.Read();
                    s = new Semestre(dr.GetInt32(0), dr.GetString(1), dr.GetString(2));
                    dr.Close();
                }
                catch (Exception e)
                {
                    Console.WriteLine("[" + System.Reflection.MethodBase.GetCurrentMethod().Name + "] " + e.Message);
                }
                finally
                {
                    CloseConnection();
                }
            }

            return s;

        }

       

        public bool eliminar(int id)
        {
            query = "DELETE FROM semestre where idSem = " + id + ";";
            if (OpenConnection())
            {
                cmd = new MySqlCommand(query, connection);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("[" + System.Reflection.MethodBase.GetCurrentMethod().Name + "] " + e.Message);
                    return false;
                }
                finally
                {
                    CloseConnection();
                }
            }
            return true;
        }

        public bool modificarConvenio(String fecha, int id)
        {
           // query = "DELETE FROM semestre where idSem = " + id + ";";
            Console.WriteLine("Fecha que recibe modificarConvenio: " +fecha+ " id: "+id);
            query = " UPDATE semestre SET fechaConvenio ='" + fecha + "' WHERE idSem = " + id + ";";
            if (OpenConnection())
            {
                cmd = new MySqlCommand(query, connection);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("[" + System.Reflection.MethodBase.GetCurrentMethod().Name + "] " + e.Message);
                    return false;
                }
                finally
                {
                    CloseConnection();
                }
            }
            return true;
        }

        public bool eliminar(string nombreSem)
        {
            
            query = "DELETE FROM semestre where nombreSem = '" + nombreSem + "';";
            if (OpenConnection())
            {
                cmd = new MySqlCommand(query, connection);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("[" + System.Reflection.MethodBase.GetCurrentMethod().Name + "] " + e.Message);
                    return false;
                }
                finally
                {
                    CloseConnection();
                }
            }
            return true;
        }
        
    }
}
